package aes.server;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import common.Question;

/**
 * This class represents manager used in order to manage questions
 *
 */
public class QuestionsManager {
	
	/**
	 * Returns all questions in Question Table
	 * @return Array of question objects
	 */
	public static Question[] getQuestionList(){
		ArrayList<Question> arr=new ArrayList<Question>();
		DBManager db = DBManager.getInstance();
		try {
			String sql ="select q.qId, q.question, q.answer1, q.answer2, q.answer3, q.answer4, q.correct, " +
					"q.teacherID, q.notes, u.lastname, u.firstname " +
					"From questions q, users u " +
					"where q.teacherId=u.id";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			ResultSet rs1 = stmt1.executeQuery();
			while(rs1.next()){
				Question q = new Question();
				q.setId(rs1.getInt("qId"));
				q.setQuestion(rs1.getString("question"));
				q.setAnswers(new String[]{rs1.getString("answer1"),rs1.getString("answer2"),rs1.getString("answer3"),rs1.getString("answer4")});
				q.setCorrectAnswer(rs1.getInt("correct"));
				q.setTeacherId(rs1.getInt("teacherID"));
				q.setTeacherName(rs1.getString("firstname")+rs1.getString("lastname"));
				q.setNotes(rs1.getString("notes"));
				arr.add(q);
			}
			rs1.close();
			Question[] ret = new Question[arr.size()];
			arr.toArray(ret);
			return ret;
		}
		catch(SQLException ex){
			return null;
		}
	}
	
	/**
	 * Returns questions array corresponded to teacher created them
	 * @param teacherId ID of the teacher
	 * @return Array of question objects
	 */
	public static Question[] getTeacherQuestions(int teacherId){
		ArrayList<Question> arr=new ArrayList<Question>();
		DBManager db = DBManager.getInstance();
		try {
			String sql ="select q.qId, q.question, q.answer1, q.answer2, q.answer3, q.answer4, q.correct, " +
					"q.teacherID, q.notes, u.lastname, u.firstname, d.dName " +
					"From questions q, users u, departments d " +
					"where q.teacherId=u.id AND CAST((q.qId/1000) AS SIGNED)=d.dId AND q.teacherId=?";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setString(1, Integer.toString(teacherId));
			ResultSet rs1 = stmt1.executeQuery();
			while(rs1.next()){
				Question q = new Question();
				q.setId(rs1.getInt("qId"));
				q.setQuestion(rs1.getString("question"));
				q.setAnswers(new String[]{rs1.getString("answer1"),rs1.getString("answer2"),rs1.getString("answer3"),rs1.getString("answer4")});
				q.setCorrectAnswer(rs1.getInt("correct"));
				q.setTeacherId(rs1.getInt("teacherID"));
				q.setTeacherName(rs1.getString("firstname")+rs1.getString("lastname"));
				q.setNotes(rs1.getString("notes"));
				q.setSubjectName(rs1.getString("dName"));
				arr.add(q);
			}
			rs1.close();
			Question[] ret = new Question[arr.size()];
			arr.toArray(ret);
			return ret;
		}
		catch(SQLException ex){
			return null;
		}
	}
	
	/**
	 * Returns questions array of all teachers
	 * @return Array of question objects
	 */
	public static Question[] getTeachersQuestions() {
		ArrayList<Question> arr=new ArrayList<Question>();
		DBManager db = DBManager.getInstance();
		try {
			String sql ="select q.qId, q.question, q.answer1, q.answer2, q.answer3, q.answer4, q.correct, " +
					"q.teacherID, q.notes, u.lastname, u.firstname, d.dName " +
					"From questions q, users u, departments d " +
					"where q.teacherId=u.id AND CAST((q.qId/1000) AS SIGNED)=d.dId";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			ResultSet rs1 = stmt1.executeQuery();
			while(rs1.next()){
				Question q = new Question();
				q.setId(rs1.getInt("qId"));
				q.setQuestion(rs1.getString("question"));
				q.setAnswers(new String[]{rs1.getString("answer1"),rs1.getString("answer2"),rs1.getString("answer3"),rs1.getString("answer4")});
				q.setCorrectAnswer(rs1.getInt("correct"));
				q.setTeacherId(rs1.getInt("teacherID"));
				q.setTeacherName(rs1.getString("firstname")+rs1.getString("lastname"));
				q.setNotes(rs1.getString("notes"));
				q.setSubjectName(rs1.getString("dName"));
				arr.add(q);
			}
			rs1.close();
			Question[] ret = new Question[arr.size()];
			arr.toArray(ret);
			return ret;
		}
		catch(SQLException ex){
			return null;
		}
	}
	
	/**
	 * Retrieves a question by Id
	 * @param id Question Id
	 * @return Question object
	 */
	public static Question getQuestion(int id){
		DBManager db = DBManager.getInstance();
		try {
			String sql ="select q.qId, q.question, q.answer1, q.answer2, q.answer3, q.answer4, q.correct, " +
					"q.teacherID, q.notes, u.lastname, u.firstname, d.dName " +
					"From questions q, users u, departments d " +
					"where q.teacherId=u.id AND CAST((q.qId/1000) AS SIGNED)=d.dId AND q.qId=?";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1, id);
			ResultSet rs1 = stmt1.executeQuery();
			if(rs1.next()){
				Question q = new Question();
				q.setId(rs1.getInt("qId"));
				q.setQuestion(rs1.getString("question"));
				q.setAnswers(new String[]{rs1.getString("answer1"),rs1.getString("answer2"),rs1.getString("answer3"),rs1.getString("answer4")});
				q.setCorrectAnswer(rs1.getInt("correct"));
				q.setTeacherId(rs1.getInt("teacherID"));
				q.setTeacherName(rs1.getString("firstname")+rs1.getString("lastname"));
				q.setNotes(rs1.getString("notes"));
				q.setSubjectName(rs1.getString("dName"));
				
				rs1.close();
				return q;
			
			}
			return null;
		}
		catch(SQLException ex){
			return null;
		}
	}
	
	/**
	 * Returns all questions correspond to examId 
	 * @param examId ID of exam
	 * @return Array of question objects
	 */
	public static Question[] getExamQuestions(int examId){
		ArrayList<Question> arr=new ArrayList<Question>();
		DBManager db = DBManager.getInstance();
		try {
			String sql ="select q.qId, q.question, q.answer1, q.answer2, q.answer3, q.answer4, q.correct, " +
					"q.teacherID, q.notes, u.lastname, u.firstname, qe.points, d.dName " +
					"From questions q, question_in_exam qe,users u, departments d " +
					"where q.qId=qe.qId AND q.teacherId=u.id AND CAST((q.qId/1000) AS SIGNED)=d.dId AND qe.eId=?";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setString(1, Integer.toString(examId));
			ResultSet rs1 = stmt1.executeQuery();
			while(rs1.next()){
				Question q = new Question();
				q.setId(rs1.getInt("qId"));
				q.setQuestion(rs1.getString("question"));
				q.setAnswers(new String[]{rs1.getString("answer1"),rs1.getString("answer2"),rs1.getString("answer3"),rs1.getString("answer4")});
				q.setCorrectAnswer(rs1.getInt("correct"));
				q.setTeacherId(rs1.getInt("teacherID"));
				q.setTeacherName(rs1.getString("firstname")+rs1.getString("lastname"));
				q.setNotes(rs1.getString("notes"));
				q.setSubjectName(rs1.getString("dName"));
				q.setPoints(rs1.getInt("points"));
				
				arr.add(q);
			}
			rs1.close();
			Question[] ret = new Question[arr.size()];
			arr.toArray(ret);
			return ret;
		}
		catch(SQLException ex){
			return null;
		}
	}
	
	/**
	 * Create question in DB
	 * @param q Question to create
	 * @return Creation result
	 */
	public static boolean addQuestion(Question q){
		DBManager db = DBManager.getInstance();
		if(q.getId()==0 || q.getQuestion()==null || q.getAnswers()==null || q.getCorrectAnswer()==0 || q.getTeacherId()==0 || q.getTeacherId()==0 ||
				q.getAnswers().length!=4 || q.getAnswers()[0].length()<1 || q.getAnswers()[1].length()<1 || q.getAnswers()[2].length()<1 || q.getAnswers()[3].length()<1 
				|| (getQuestion(q.getId())!=null))
			return false;
		try {
			String sql ="INSERT INTO questions (`qId`,`question`,`answer1`,`answer2`,`answer3`, "+
												"`answer4`,`correct`,`teacherID`, `notes`) " +
												"VALUES(?,?,?,?,?,?,?,?,?);";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1, q.getId());
			stmt1.setString(2, q.getQuestion());
			stmt1.setString(3, q.getAnswers()[0]);
			stmt1.setString(4, q.getAnswers()[1]);
			stmt1.setString(5, q.getAnswers()[2]);
			stmt1.setString(6, q.getAnswers()[3]);
			stmt1.setInt(7, q.getCorrectAnswer());
			stmt1.setInt(8, q.getTeacherId()); 
			stmt1.setString(9, q.getNotes());
			stmt1.executeUpdate();
			return true;
		}
		catch(SQLException ex){
			return false;
		}
	}
	
	/**
	 * Remove question from questions table by question ID
	 * @param id Question ID
	 * @return Delete result
	 */
	public static boolean removeQuestion(int id){
		if(id<100000){
			DBManager db = DBManager.getInstance();
			try {
				String sql ="DELETE FROM questions "+
						"WHERE qId=?;";
				PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
				stmt1.setInt(1, id);
				stmt1.executeUpdate();
				return true;
			}
			catch(SQLException ex){
				return false;
			}
		}
		return false;
	}
	
	/**
	 * Update existing tuple in questions table
	 * @param q Question to update
	 * @return Update result
	 */
	public static boolean updateQuestion(Question q){
		DBManager db = DBManager.getInstance();
		try {
			String sql ="UPDATE questions "+
						"SET `question` = ?, `answer1` = ?, `answer2` = ?, `answer3` = ?, "+
						"`answer4` = ?, `correct` = ?,`teacherID` = ? , `notes` = ?"+
						"WHERE qId=?;";
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setString(1, q.getQuestion());
			stmt1.setString(2, q.getAnswers()[0]);
			stmt1.setString(3, q.getAnswers()[1]);
			stmt1.setString(4, q.getAnswers()[2]);
			stmt1.setString(5, q.getAnswers()[3]);
			stmt1.setInt(6, q.getCorrectAnswer());
			stmt1.setInt(7, q.getTeacherId());
			stmt1.setString(8, q.getNotes());
			stmt1.setInt(9, q.getId());
			stmt1.executeUpdate();
			return true;
		}
		catch(SQLException ex){
			return false;
		}
	}
	
	
}